package org.xtframe.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import org.xtframe.dao.DataBaseDao;
import org.xtframe.sql.PreparedParameter;
import org.xtframe.util.CommonUtil;
import org.xtframe.util.SystemConfigUtil;
import org.xtframe.vo.FieldVO;
import org.xtframe.vo.TableColumnsVO;
import org.xtframe.vo.TableFieldVO;
import org.xtframe.vo.TableStatusVO;

//@Repository("dataBaseDao")
@Transactional
public class MysqlDataBaseDaoImpl implements DataBaseDao {
	private SessionFactory sessionFactory;
	
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public List<Map<String, Object>> executeQuery(String sql, List<PreparedParameter> preparedParameters) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			setPreparedParameters(pstmt, preparedParameters);
			rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			List<FieldVO> fields = new ArrayList<FieldVO>(columnCount);
			FieldVO field = null;
			for (int i = 1; i <= columnCount; i++) {
				field = new FieldVO();
				field.setName(rsmd.getColumnLabel(i).toLowerCase());
				field.setInt(rsmd.getColumnTypeName(i).contains("INT"));
				fields.add(field);
			}
			rsmd = null;
			Map<String, Object> row = null;
			while (rs.next()) {
				row = new HashMap<String, Object>(columnCount);
				for (FieldVO obj : fields) {
					if (obj.isInt()) {
						row.put(obj.getName(), rs.getInt(obj.getName()));
					} else {
						row.put(obj.getName(), rs.getString(obj.getName()));
					}
				}
				rows.add(row);
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			rows = null;
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public List<String> executeQuery(String sql, int columnIndex, List<PreparedParameter> preparedParameters) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		List<String> rows = new ArrayList<String>();
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			setPreparedParameters(pstmt, preparedParameters);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				rows.add(rs.getString(columnIndex));
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			rows = null;
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public int executeUpdate(String sql, List<PreparedParameter> preparedParameters) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int rows = 0;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			setPreparedParameters(pstmt, preparedParameters);
			rows = pstmt.executeUpdate();
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			rows = -1;
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}
	
	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public int[] excuteTransaction(List<String> sqlList, List<List<PreparedParameter>> preparedParametersList){
		Connection conn = null;
		PreparedStatement pstmt = null;
		int[] rows = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement("");
			for(int i=0; i<sqlList.size(); i++){
				pstmt.addBatch(sqlList.get(i));
				if(preparedParametersList!=null && preparedParametersList.size()>i) 
					setPreparedParameters(pstmt, preparedParametersList.get(i));
			}
			rows = pstmt.executeBatch();
			tran.commit();
		} catch (Exception e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public Map<String, Object> execute(String sql) {
		Session session = null;
		Connection conn = null;
		Transaction tran = null;
		Map<String, Object> infos = new HashMap<String, Object>();
		Statement pstmt = null;
		try {
			session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.createStatement();
			pstmt.setEscapeProcessing(false);
			boolean result = pstmt.execute(sql);
			int num = 0;
			if (result) {
				ResultSet rs = pstmt.executeQuery(sql);
				if (rs.last()) {
					num = rs.getRow();
				}
			} else {
				num = pstmt.getUpdateCount();
			}
			infos.put("sucess", num);
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			infos.put("error", e.getMessage());
			infos.put("errorCode", e.getErrorCode());
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				infos.put("error", e.getMessage());
				infos.put("errorCode", e.getErrorCode());
			}
		}
		return infos;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public int insert(String sql, List<PreparedParameter> preparedParameters) throws Exception {
		int id = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			int rows = pstmt.executeUpdate();
			if (rows > 0) {
				ResultSet rs = pstmt.executeQuery("SELECT last_insert_id()");
				if (rs.next()) {
					id = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public String findFirst(String sql, int columnIndex) {
		String result = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getString(columnIndex);
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public int findRows(String sql) {
		int line = -1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				line = rs.getInt(1);
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return line;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public long findTableSize(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		long dataSize = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				dataSize += rs.getLong("Data_length");
				dataSize += rs.getLong("Index_length");
			}
			tran.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			if (tran != null) {
				tran.rollback();
			}
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return dataSize;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public List<TableFieldVO> findTableFields(String tableName) {
		List<TableFieldVO> fieldVOs = new ArrayList<TableFieldVO>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement("SHOW FIELDS FROM " + tableName);
			rs = pstmt.executeQuery();
			TableFieldVO fieldVO = null;
			while (rs.next()) {
				fieldVO = new TableFieldVO();
				fieldVO.setField(rs.getString("Field"));
				fieldVO.setType(rs.getString("Type"));
				fieldVO.setAllowNull(rs.getString("Null"));
				fieldVO.setKey(rs.getString("Key"));
				fieldVO.setDefaultValue(rs.getString("Default"));
				fieldVO.setExtra(rs.getString("Extra"));
				fieldVOs.add(fieldVO);
			}
			tran.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			tran.rollback();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return fieldVOs;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public List<TableColumnsVO> findTableColumns(String tableName) {
		List<TableColumnsVO> fullColumnsVOs = new ArrayList<TableColumnsVO>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn
					.prepareStatement("SHOW FULL COLUMNS FROM " + tableName);
			rs = pstmt.executeQuery();
			TableColumnsVO fullColumnsVO = null;
			while (rs.next()) {
				fullColumnsVO = new TableColumnsVO();
				fullColumnsVO.setField(rs.getString("Field"));
				fullColumnsVO.setType(rs.getString("Type"));
				fullColumnsVO.setCollation(rs.getString("Collation"));
				fullColumnsVO.setAllowNull(rs.getString("Null"));
				fullColumnsVO.setKey(rs.getString("Key"));
				fullColumnsVO.setDefaultValue(rs.getString("Default"));
				fullColumnsVO.setExtra(rs.getString("Extra"));
				fullColumnsVO.setPrivileges(rs.getString("Privileges"));
				fullColumnsVO.setComment(rs.getString("Comment"));
				fullColumnsVOs.add(fullColumnsVO);
			}
			tran.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			tran.rollback();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return fullColumnsVOs;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public List<TableStatusVO> findTableStatus(String sql) {
		List<TableStatusVO> tableStatusVOs = new ArrayList<TableStatusVO>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			TableStatusVO tableStatusVO = null;
			while (rs.next()) {
				tableStatusVO = new TableStatusVO();
				tableStatusVO.setName(rs.getString("Name"));
				tableStatusVO.setEngine(rs.getString("Engine"));
				tableStatusVO.setRows(rs.getLong("Rows"));
				tableStatusVO.setData_length(rs.getLong("Data_length"));
				tableStatusVO.setIndex_length(rs.getLong("Index_length"));
				tableStatusVO.setData_free(rs.getLong("Data_free"));
				tableStatusVO.setAuto_increment(rs.getString("Auto_increment"));
				tableStatusVO.setCollation(rs.getString("Collation"));
				tableStatusVOs.add(tableStatusVO);
			}
			tran.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			tran.rollback();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tableStatusVOs;
	}
	
	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public long sequenceNextVal(String name) {
		long id = 0;
		Connection conn = null;
		Statement stmt = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			stmt = conn.createStatement();
			stmt.setEscapeProcessing(false);
			int rows = stmt.executeUpdate(CommonUtil.createSql(SystemConfigUtil.getSystemConfig().getNextvalRegSequence1Sql(), name));
			if (rows > 0) {
				ResultSet rs = stmt.executeQuery(CommonUtil.createSql(SystemConfigUtil.getSystemConfig().getNextvalRegSequence2Sql(), name));
				if (rs.next()) {
					id = rs.getLong(1);
				}
				rs.close();
				rs = null;
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
			id = -1L;
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;
	}
	
	public static void setPreparedParameters(PreparedStatement pstmt, List<PreparedParameter> preparedParameters) throws SQLException{
		if(preparedParameters != null){
			for(int i=0; i<preparedParameters.size(); i++){
				PreparedParameter pp = preparedParameters.get(i);
				int sequen = pp.getSequen()==-1 ? i+1 : pp.getSequen();
				pstmt.setString(sequen, pp.getData().toString());
			}
		}
	}
}
